﻿/***********************************************************************************************************************************
 *    Name: Compression.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
**/
IF OBJECT_ID(N'TempDB..#Compression_ROW' ) IS NOT NULL DROP TABLE #Compression_ROW;
IF OBJECT_ID(N'TempDB..#Compression_PAGE') IS NOT NULL DROP TABLE #Compression_PAGE;

CREATE TABLE #Compression_ROW (
	object_name										SYSNAME	NOT Null,
	schema_name										SYSNAME	NOT Null,
	index_id										INT		NOT Null,
	partition_number								INT		NOT Null,
	size_with_current_compression_setting			BIGINT		Null,
	size_with_requested_compression_setting			BIGINT		Null,
	sample_size_with_current_compression_setting	BIGINT		Null,
	sample_size_with_requested_compression_setting	BIGINT		Null);
SELECT * INTO #Compression_PAGE FROM #Compression_ROW;

INSERT INTO #Compression_ROW  EXECUTE sys.sp_estimate_data_compression_savings @schema_name= N'dbo', @object_name= N'TBL_DAIUS_GL_DRILL', @data_compression= 'ROW',  @index_id= Null, @partition_number= Null;
INSERT INTO #Compression_PAGE EXECUTE sys.sp_estimate_data_compression_savings @schema_name= N'dbo', @object_name= N'TBL_DAIUS_GL_DRILL', @data_compression= 'PAGE', @index_id= Null, @partition_number= Null;

WITH
	r (schema_name, object_name, size_with_current, size_with_row ) AS (SELECT schema_name, object_name, SUM(size_with_current_compression_setting), SUM(size_with_requested_compression_setting) FROM #Compression_ROW  GROUP BY schema_name, object_name),
	p (schema_name, object_name,                    size_with_page) AS (SELECT schema_name, object_name,                                             SUM(size_with_requested_compression_setting) FROM #Compression_PAGE GROUP BY schema_name, object_name)
SELECT
	r.schema_name, r.object_name, r.size_with_current, r.size_with_row, p.size_with_page,
	[% row]	= CAST(100.0*r.size_with_row /r.size_with_current AS DECIMAL(6,2)),
	[% page]= CAST(100.0*p.size_with_page/r.size_with_current AS DECIMAL(6,2))
  FROM r INNER JOIN p ON (r.object_name = p.object_name AND r.schema_name = p.schema_name);


ALTER TABLE dbo.TBL_DAIUS_GL_DRILL REBUILD PARTITION= ALL WITH (DATA_COMPRESSION= PAGE);
